
const xlsx = require('node-xlsx');
const fs = require('fs');
const path = require('path');
const mysql = require('mysql');
const progress = require('cli-progress');

const dbConfig = {
  host: '127.0.0.1',
  user: 'root',
  password: 'root',
  database: 'excel_import',
  connectTimeout: 20000 // 增加连接超时时间
};

const _file = `${__dirname}/excel/`;
const filterButNeed = []
const UNIQUE_KEY = '统一社会信用代码'; // 假设这是主键字段
function createTable (conn, tableName, headers, callback) {
  tableName = "xlsxSet1";


  const COLUMN_DEFINITIONS = {
    [UNIQUE_KEY]: 'VARCHAR(30) NOT NULL UNIQUE',// PRIMARY KEY
    // '所属运营商': 'VARCHAR(20)',
    // '手机号归属省': 'VARCHAR(20)'
  };

  const columns = headers.map(h => {
    const type = COLUMN_DEFINITIONS[h] || 'VARCHAR(50)';
    return `\`${h}\` ${type}`;
  }).join(',');

  // console.log("columns", columns);
  const indexQueries = [
    // `CREATE INDEX idx_company_name ON \`${tableName}\`(\`公司名称\`)`,
    // `CREATE INDEX idx_reg_date ON \`${tableName}\`(\`成立日期\`)`,
    // `CREATE INDEX idx_status ON \`${tableName}\`(\`企业状态\`)`,
    // `ALTER TABLE \`${tableName}\` ADD CONSTRAINT uc_credit_code UNIQUE (\`统一社会信用代码\`)`,
    // `ALTER TABLE \`${tableName}\` ADD CONSTRAINT uc_reg_number UNIQUE (\`注册号\`)`
  ];

  conn.beginTransaction(err => {
    if (err) return callback(err);

    // 创建主表
    conn.query(`CREATE TABLE IF NOT EXISTS \`${tableName}\` (
      id INT  AUTO_INCREMENT PRIMARY KEY,
      ${columns}
    )  AUTO_INCREMENT=1 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`, (err) => {
      if (err) {
        conn.rollback();
        return callback(err);
      }

      // 批量创建索引
      let completed = 0;
      indexQueries.forEach(query => {
        conn.query(query, (err) => {
          if (err) {
            conn.rollback();
            return callback(err);
          }
          if (++completed === indexQueries.length) {
            conn.commit(callback);
          }
        });
      });

      if (!indexQueries.length) {
        callback()
      }
    });
  });
}

function batchInsert (conn, tableName, data, callback) {
  tableName = "xlsxSet1";
  const headers = Object.keys(data[0]);
  const columnCount = headers.length;
  const rowPlaceholder = `(${headers.map(() => '?').join(',')})`;

  // 创建进度条
  const bar = new progress.SingleBar({
    format: '数据插入进度 |{bar}| {percentage}% | {value}/{total} 条',
    barCompleteChar: '\u2588',
    barIncompleteChar: '\u2591',
    hideCursor: true
  });
  bar.start(data.length, 0);

  let processed = 0;
  const batchSize = 1000;
  let affectedRowsTotal = 0

  function insertBatch (startIndex) {
    const batch = data.slice(startIndex, startIndex + batchSize);
    const rowCount = batch.length;
    const placeholders = Array(rowCount).fill(rowPlaceholder).join(',');
    const sql = `INSERT IGNORE INTO \`${tableName}\` (${headers.map(h => `\`${h}\``).join(',')})
                 VALUES ${placeholders}`;
    const values = batch.flatMap(row => headers.map(h => {
      if (h === UNIQUE_KEY && row[h] === "-") {
        filterButNeed.push(row)
      }
      return row[h] === undefined ? null : row[h]
    }));

    function replaceSlots (template, values) {
      let index = 0;
      return template.replace(/\?/g, () => {
        return index < values.length ? values[index++] : '?';
      });
    }

    // console.log(replaceSlots(sql, values))
    conn.query(sql, values, (err, result) => {
      if (err) {
        bar.stop();
        console.error('插入失败:', err);
        return callback(err);
      }

      processed += rowCount;
      bar.update(processed);
      if (processed < data.length) {
        insertBatch(processed);
        affectedRowsTotal += result.affectedRows
      } else {
        bar.stop();
        callback(null, {
          affectedRows: affectedRowsTotal,
          duplicateCount: data.length - affectedRowsTotal
        });
      }
    });
  }

  insertBatch(0);
}

let allTotalCount = 0, allInsetCount = 0, allDuplicateCount = 0, currentNeedIndex = 0;
function processExcelFiles (callback, conn) {
  const excelFiles = [];
  // 创建文件处理进度条
  const fileBar = new progress.SingleBar({
    format: '文件处理进度 |{bar}| {percentage}% | {value}/{total} 个文件',
    barCompleteChar: '\u2588',
    barIncompleteChar: '\u2591',
    hideCursor: true
  });

  function findExcelFiles (dir) {
    const items = fs.readdirSync(dir);
    items.forEach(item => {
      const fullPath = path.join(dir, item);
      const stat = fs.statSync(fullPath);
      if (stat.isDirectory()) {
        findExcelFiles(fullPath);
      } else if (
        (item.endsWith('.xlsx') || item.endsWith('.xls')) &&
        !item.startsWith('~$') &&
        !item.startsWith('WSP')
      ) {
        excelFiles.push(fullPath);
      }
    });
  }

  findExcelFiles(_file);
  fileBar.start(excelFiles.length, 0);
  let processedFiles = 0;

  function processNextFile () {
    if (processedFiles >= excelFiles.length) {
      fileBar.stop();
      conn.end();
      return callback();
    }

    const filePath = excelFiles[processedFiles++];
    fileBar.update(processedFiles);
    const fileName = path.basename(filePath);
    console.log(fileName);

    try {
      const workSheets = xlsx.parse(filePath);
      const sheet = workSheets[0];
      const headers = sheet.data[1]//sheet.data[0].filter((_, index) => [0, 1, 2].indexOf(index) > -1);// 9,10,11

      const rows = (workSheets.map(item => {
        return item.data.slice(2)//.map(item2 => [item2[0], item2[1], item2[2]]);
      }))[0]
      const totalCount = rows.length;
      console.log(headers, totalCount, rows[0]);

      const jsonData = rows.map(row => {
        return headers.reduce((obj, key, i) => {
          obj[key] = row[i] || '';
          return obj;
        }, {});
      });

      createTable(conn, "xlsxSet1", headers, (err) => {
        if (err) return callback(err);
        let currentFilterButNeedCount = filterButNeed.length;
        batchInsert(conn, "xlsxSet1", jsonData, (err, result) => {
          if (err) return callback(err);
          //   if (filterButNeed > 200000 || excelFiles.length == processedFiles) {
          //     let dataList = [{
          //       name: 'sheet1',
          //       data: [headers.concat(filterButNeed.slice(currentNeedIndex * 200000, 200000))]
          //     }]
          //     currentNeedIndex++;
          //     writeExcel(dataList, currentNeedIndex)
          //   }
          console.log(`\n文件 ${fileName} 处理完成：
          - 当前文件总数据量: ${totalCount} 条
          - 当前文件成功插入: ${result.affectedRows} 条
          - 当前文件重复跳过: ${result.duplicateCount} 条
          - 当前文件${UNIQUE_KEY}为“-”的: ${filterButNeed.length - currentFilterButNeedCount} 条
          - 文件总数: ${allTotalCount += totalCount} 条
          - 文件总插入: ${allInsetCount += result.affectedRows} 条
          - 文件总重复: ${allDuplicateCount += result.duplicateCount} 条
          - ${UNIQUE_KEY}为“-”的: ${filterButNeed.length} 条
          `);
          processNextFile();
        });
      });
    } catch (err) {
      console.error(`\n处理文件 ${fileName} 时出错:`, err);
      processNextFile();
    }
  }

  processNextFile();
}



function writeExcel (data, fileName) {
  console.time()
  let newlist = data[0].data.slice(1).map((item, index) => {
    let temp = {}
    data[0].data[0].forEach((item2, index2) => { temp[item2] = item[index2] })
    return temp
  })
  console.log(`🍬开始写入：${_output}${fileName}.xlsx`, newlist.length);

  // console.log(JSON.stringify(newlist[0], null, 2));
  const ws = XLSX.utils.json_to_sheet(newlist);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  // 生成Excel文件
  XLSX.writeFile(wb, `${_output}${fileName}.xlsx`, {
    compression: true,  // ✔️ 关键压缩选项
    bookSST: true,      // 启用共享字符串表
    bookType: 'xlsx'    // 指定输出格式
  });
  console.log(`👌完成合并：${_output}${fileName}.xlsx`)
  console.timeEnd()

}

(function () {
  const conn = mysql.createConnection(dbConfig);
  conn.connect((err) => {
    if (err) {
      console.error('数据库连接失败:', err);
      return;
    }
    console.log('数据库连接成功');
    processExcelFiles((err) => {
      if (err) console.error(err);
      else console.log('所有文件处理完成');
      setTimeout(() => {
        // conn.end();
      }, 10000);
    }, conn);
  });
})();


// SELECT * FROM `xlsxset1` e1
// WHERE e1.`所属运营商`='联通' AND e1.`手机号归属省` NOT IN ('北京市','天津市','重庆市','陕西省','山西省','青海市','海南省','福建省')